#! /usr/bin/env python

import sqlite3
import SQLConn
import Parser

class AccessDB(SQLConn.SQLConn):
	def __init__ (self, DB_File):
		self.db = sqlite3.connect(DB_File).cursor()

	def GetURLs(self, Num):
		#url=self.db.execute ("SELECT Host.HOST,URList.PATH FROM URList JOIN Host ON URList.HOST_ID = Host.HOST_ID  WHERE URList.STATUS='0' ORDER BY Host.PAGE_COUNT DESC LIMIT 0,%s" % (Num) ).fetchone()
		url=self.db.execute ("SELECT Host.HOST,URList.PATH FROM URList JOIN Host ON URList.HOST_ID = Host.HOST_ID  WHERE URList.STATUS='0' ORDER BY Host.PAGE_COUNT LIMIT %d,%d" % (Num-1, Num) ).fetchone()
		url = "http://"+url[0].encode('ascii','ignore') +url[1].encode('ascii','ignore')
		return url

	def AddContents (self, url , contents):
		url_id = self.url2url_id (url)
		# Insert a content to 'Content' table
		try:
			self.insert2table ("Content" , "('%s', '%s')" % (url_id , contents))
		except:
			pass
		# Update URList Status
		self.db.execute("UPDATE URList SET STATUS='1' WHERE URL_ID=%d" % (url_id))
		self.db.executescript("")

	def AddHost (self, host):
		host_row= self.db.execute("SELECT HOST,HOST_ID FROM Host WHERE HOST='%s' LIMIT 0,1" % (host.encode('ascii','ignore'))).fetchone()
		if host_row==None:
			self.insert2table("Host", "((SELECT max(HOST_ID) FROM HOST)+1 ,'%s' , 1)" % (host.encode('ascii','ignore')))
			self.db.executescript("")
			return self.db.execute ("SELECT HOST_ID FROM Host WHERE HOST='%s' LIMIT 0,1" % (host.encode('ascii','ignore'))).fetchone()[0]
		else:
			self.db.executescript("UPDATE Host SET PAGE_COUNT=PAGE_COUNT+1 WHERE HOST='%s' ;" % (host.encode('ascii','ignore')))
			return host_row[1]

	def AddURL  (self, host_id , path):
		path_row= self.db.execute("SELECT URL_ID FROM URList WHERE HOST_ID='%s' AND PATH='%s' LIMIT 0,1" % (host_id, path)).fetchone()
		if path_row==None:
			self.insert2table("URList", "((SELECT max(URL_ID) FROM URList)+1 ,%d , '%s', 1 , '0')" % (host_id, path))
			self.db.executescript("")
		else:
			self.db.executescript("UPDATE URList SET LINKED_COUNT=LINKED_COUNT+1 WHERE HOST_ID=%d ;" % (path_row[0]))

	def Close(self):
		self.db.close()

	def url2host_id (self, url):
		host= Parser.URLHost(url)
		host_id = self.db.execute ("SELECT HOST_ID FROM Host WHERE HOST='%s'" % (host)).fetchone()[0]
		return host_id

	def url2url_id (self, url):
		host_id = self.url2host_id(url)
		path= Parser.URLPath(url)
		url_id = self.db.execute ("SELECT URL_ID FROM URList WHERE HOST_ID=%d AND PATH='%s'" % (host_id , path)).fetchone()[0]
		return url_id
